﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class BATCHMOVData : BaseFunction
    {
        public bool flagRegenerateLastMonthData;
        ArrayList ListTris = new ArrayList();

        List<WGSummary> ListWGSummary;
        string TrisInclude = "";
        DataSet set4Return = new DataSet();
        public int ThreadCount;
        private string OrderNum;
        DataTable tbWGSummary;
        public BATCHMOVData()
            : base()
        {
            BuildTrisList();
        }
        public BATCHMOVData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        private void BuildTrisList()
        {
            ListTris = new ArrayList();
            TrisType tristype = null;
            TrisInclude = "01;10;11;13;";
            tristype = new TrisType("01", "+", "净长"); ListTris.Add(tristype);
            tristype = new TrisType("10", "+", "货物收入"); ListTris.Add(tristype);
            tristype = new TrisType("11", "+", "货物收入"); ListTris.Add(tristype);
            tristype = new TrisType("12", "-", "销售返回"); ListTris.Add(tristype);
            tristype = new TrisType("13", "+", "产品录入"); ListTris.Add(tristype);
            tristype = new TrisType("20", "-", "货物发出"); ListTris.Add(tristype);
            tristype = new TrisType("21", "+", "货物进入"); ListTris.Add(tristype);
            tristype = new TrisType("26", "-", "旧布卷减少"); ListTris.Add(tristype);
            tristype = new TrisType("27", "+", "新布卷增加"); ListTris.Add(tristype);
            tristype = new TrisType("31", "-", "发货"); ListTris.Add(tristype);
            tristype = new TrisType("32", "+", "退货"); ListTris.Add(tristype);
            tristype = new TrisType("36", "-", "生产消耗"); ListTris.Add(tristype);
            tristype = new TrisType("40", "-", "等级修改减少"); ListTris.Add(tristype);
            tristype = new TrisType("41", "-", "等级修改增加"); ListTris.Add(tristype);
            tristype = new TrisType("42", "+", "库存修正减少"); ListTris.Add(tristype);
            tristype = new TrisType("43", "+", "库存修正增加"); ListTris.Add(tristype);
            tristype = new TrisType("48", "-", "生产差异-"); ListTris.Add(tristype);
            tristype = new TrisType("49", "+", "生产差异+"); ListTris.Add(tristype);
            tristype = new TrisType("51", "+", "消耗差异"); ListTris.Add(tristype);
            tristype = new TrisType("61", "-", "留位"); ListTris.Add(tristype);
            tristype = new TrisType("62", "+", "取消留位"); ListTris.Add(tristype);
            tristype = new TrisType("64", "+", "留位剪布"); ListTris.Add(tristype);
            tristype = new TrisType("65", "+", "锁定"); ListTris.Add(tristype);
            tristype = new TrisType("66", "+", "解锁"); ListTris.Add(tristype);
            tristype = new TrisType("69", "+", "其他修正"); ListTris.Add(tristype);
            tristype = new TrisType("80", "+", "发票数量"); ListTris.Add(tristype);
            tristype = new TrisType("88", "-", "价值更改-"); ListTris.Add(tristype);
            tristype = new TrisType("89", "+", "价值更改"); ListTris.Add(tristype);
            tristype = new TrisType("IN", "+", "系统自动调节"); ListTris.Add(tristype);

        }
        public DataSet GetIncomeData(DataRow dwABPOS)
        {
            DataSet setLocal = new DataSet();
            DataTable tableLocal = null;
            string WBSRTDATE, WBENTDATE;
            WBSRTDATE = dwABPOS["WBSRTDATE"].ToString().Trim();
            WBENTDATE = dwABPOS["WBENTDATE"].ToString().Trim();
            string BATCHNO;
            BATCHNO = dwABPOS["BATCHNO"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                #region                
                or1.CommandText = " SELECT B.WBBDPART,B.WBBEINDAT AS WBEWBUCHDAT,COUNT(A.WBEWSTKNR) AS QTY,SUM(A.WBEWMTR/1000) AS MTR, "
                    + " A.WBEWAUFKRS||'-'||TO_NUMBER(A.WBEWAUFLFD)||'-'||TO_NUMBER(A.WBEWAUFPOS) AS ORDERNO,'' AS LSNAME1, "
                    + " TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE)||'-'||A.WBEWVAR AS ARTICLE, "
                    + " A.WBEWAUFKRS,A.WBEWAUFLFD,A.WBEWAUFPOS "
                    + " FROM WBBEW A ,WBBST B WHERE A.WBEWMANDANT = B.WBBMANDANT "
                    + " AND A.WBEWRWKRS = B.WBBRWKRS AND A.WBEWRWLFD = B.WBBRWLFD "
                    + " AND A.WBEWPOS = B.WBBPOS AND (A.WBEWBS IN ('01','10') OR (A.WBEWBS = '13' AND trim(A.WBEWFC) = 'WB15')) "
                    + " AND A.WBEWWAKZ = 'R' AND B.WBBDPART LIKE '" + BATCHNO + "%'"
                    + " AND B.WBBEINDAT>='" + WBSRTDATE + "' AND B.WBBEINDAT<='" + WBENTDATE + "'"
                    + " GROUP BY B.WBBDPART,B.WBBEINDAT,A.WBEWAUFKRS,A.WBEWAUFLFD,A.WBEWAUFPOS,A.WBEWART,A.WBEWFARBE,A.WBEWVAR ";
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "INCOMEDATA";
                    SetDefault(tableLocal);
                    
                    setLocal.AcceptChanges();
                    foreach (DataTable dtTemp in setLocal.Tables)
                    {
                        DataSet setUI = new DataSet();
                        setUI = ImportDataTable(setUI, dtTemp);
                        DataTable tableABPOS = setUI.Tables["INCOMEDATA"];
                        foreach (DataRow dwTmp in tableABPOS.Rows)
                        {
                            setUI = GetABFOLGE(setUI, dwTmp);
                            setUI = GetABAUFM(setUI, dwTmp);
                        }

                        if (set4Return.Tables.Contains("INCOMEDATA"))
                        {
                            set4Return.Tables["INCOMEDATA"].Clear();
                        }
                        set4Return.Merge(setUI);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet GetIssueData(DataRow dwABPOS)
        {
            
            DataSet setLocal = new DataSet();
            DataTable tableLocal = null;
            string WBSRTDATE, WBENTDATE;
            WBSRTDATE = dwABPOS["WBSRTDATE"].ToString().Trim();
            WBENTDATE = dwABPOS["WBENTDATE"].ToString().Trim();
            string BATCHNO;
            BATCHNO = dwABPOS["BATCHNO"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                #region
                or1.CommandText = " SELECT B.WBBDPART,A.WBEWBUCHDAT,COUNT(A.WBEWSTKNR) AS QTY,SUM(A.WBEWMTR/1000) AS MTR, "
                    + " A.WBEWAUFKRS||'-'||TO_NUMBER(A.WBEWAUFLFD)||'-'||TO_NUMBER(A.WBEWAUFPOS) AS ORDERNO,'' AS LSNAME1,"
                    + " TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE)||'-'||A.WBEWVAR AS ARTICLE, "
                    + " A.WBEWAUFKRS,A.WBEWAUFLFD,A.WBEWAUFPOS "
                    + " FROM WBBEW A ,WBBST B "
                    + " WHERE A.WBEWMANDANT = B.WBBMANDANT AND A.WBEWRWKRS = B.WBBRWKRS  "
                    + " AND A.WBEWRWLFD = B.WBBRWLFD AND A.WBEWPOS = B.WBBPOS "
                    + " AND A.WBEWWAKZ = 'R' AND A.WBEWBS = '20' AND trim(A.WBEWFC) = 'AV10' AND B.WBBDPART LIKE '" + BATCHNO + "%'"
                    + " AND A.WBEWBUCHDAT>='" + WBSRTDATE + "' AND A.WBEWBUCHDAT<='" + WBENTDATE + "'"
                    + " GROUP BY B.WBBDPART,A.WBEWBUCHDAT,A.WBEWAUFKRS,A.WBEWAUFLFD,A.WBEWAUFPOS,A.WBEWART,A.WBEWFARBE,A.WBEWVAR "
                    + " union all "
                    + " select B.WBBDPART,A.WBEWBUCHDAT,COUNT(A.WBEWSTKNR) AS QTY,SUM(A.WBEWMTR/1000) AS MTR, "
                    + " A.WBEWLO||'-'||A.WBEWLB||'-'||A.WBEWLP AS ORDERNO,'' AS LSNAME1, "
                    + " TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE)||'-'||A.WBEWVAR AS ARTICLE, "
                    + " A.WBEWAUFKRS,A.WBEWAUFLFD,A.WBEWAUFPOS "
                    + " FROM WBBEW A ,WBBST B "
                    + " WHERE A.WBEWMANDANT = B.WBBMANDANT AND A.WBEWRWKRS = B.WBBRWKRS "
                    + " AND A.WBEWRWLFD = B.WBBRWLFD AND A.WBEWPOS = B.WBBPOS "
                    + " AND A.WBEWWAKZ = 'R' AND A.WBEWBS = '21' AND trim(A.WBEWFC) = 'WB07' "
                    + " AND A.WBEWLB = 'EX' AND B.WBBDPART LIKE '" + BATCHNO + "%'"
                    + " AND A.WBEWBUCHDAT>='" + WBSRTDATE + "' AND A.WBEWBUCHDAT<='" + WBENTDATE + "'"
                    + " GROUP BY B.WBBDPART,A.WBEWBUCHDAT,A.WBEWLO,A.WBEWLB,A.WBEWLP,A.WBEWAUFKRS,A.WBEWAUFLFD,A.WBEWAUFPOS,A.WBEWART,A.WBEWFARBE,A.WBEWVAR ";
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "ISSUEDATA";
                    SetDefault(tableLocal);
                    setLocal.AcceptChanges();
                    foreach (DataTable dtTemp in setLocal.Tables)
                    {
                        DataSet setUI = new DataSet();
                        setUI = ImportDataTable(setUI, dtTemp);
                        DataTable tableABPOS = setUI.Tables["ISSUEDATA"];
                        foreach (DataRow dwTmp in tableABPOS.Rows)
                        {
                            setUI = GetABFOLGE(setUI, dwTmp);
                        }
                                                
                        if (set4Return.Tables.Contains("ISSUEDATA"))
                        {
                            set4Return.Tables["ISSUEDATA"].Clear();
                        }
                        set4Return.Merge(setUI);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet GetABFOLGE(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "ABFOLGE";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT B.LSNAME1 FROM ABFOLGE A,LIEFDAT B "
                    + " WHERE A.ABFMANDANT = B.LSMANDANT AND A.ABFLIEFNR = B.LSLIEFNR "
                    + " AND ABFMANDANT = '00' AND ABFAUFKRS = :ABFAUFKRS  "
                    + " AND ABFAUFLFD = :ABFAUFLFD  AND ABFAUFPOS = :ABFAUFPOS  "
                     ;
                orlReadCommand.Parameters.Add("ABFAUFKRS", OracleType.NVarChar).Value = dwABPOS["WBEWAUFKRS"].ToString().Trim();
                orlReadCommand.Parameters.Add("ABFAUFLFD", OracleType.NVarChar).Value = dwABPOS["WBEWAUFLFD"].ToString().Trim();
                orlReadCommand.Parameters.Add("ABFAUFPOS", OracleType.NVarChar).Value = dwABPOS["WBEWAUFPOS"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "";
                    foreach (DataRow dw in dsResult.Tables["ABFOLGE"].Rows)
                    {
                        ColumnName = "LSNAME1";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;

                        dwABPOS["LSNAME1"] = dw["LSNAME1"];
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetABAUFM(DataSet dsTarget, DataRow dwABPOS)
        {

            string TableName = "ABAUFM";
            try
            {  
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT distinct ABADIAUFKRS||'-'||TO_NUMBER(ABADIAUFLFD)||'-'||TO_NUMBER(ABADIAUFPOS) AS DIORDER,ABADIAUFKRS "               
                    + " FROM ABAUFM,ABPOS  WHERE ABAAUFKRS = ABPAUFKRS AND ABAAUFLFD = ABPAUFLFD  "
                    + " AND ABAAUFPOS = ABPAUFPOS AND ABAAPOS ='0001' AND ABPAUFART = 'PR' "
                    + " AND  ABAAUFKRS = :ABAAUFKRS AND ABAAUFLFD=:ABAAUFLFD AND ABAAUFPOS=:ABAAUFPOS";
                orlReadCommand.Parameters.Add("ABAAUFKRS", OracleType.NVarChar).Value = dwABPOS["WBEWAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABAAUFLFD", OracleType.NVarChar).Value = dwABPOS["WBEWAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABAAUFPOS", OracleType.NVarChar).Value = dwABPOS["WBEWAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "ABAUFM";
                    
                    foreach (DataRow dwTemp in dsResult.Tables["ABAUFM"].Rows)
                    {
                        if (dwTemp["ABADIAUFKRS"].ToString() != "  ")
                        {
                            dwABPOS["ORDERNO"]= dwTemp["DIORDER"].ToString() ;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;

        }
        public DataSet BuildBATCH(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetIncomeData(dwUI);
                setResult = GetIssueData(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}